package com.dean.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.dean.dao.DeanToolDao;
import com.dean.entity.DeanExportInfo;
import com.dean.entity.DeanImportInfo;
import com.dean.entity.SysDbConnInfo;
import com.dean.exception.DeanRuntimeException;
import com.dean.service.DataFileImpExpService;
import com.dean.utils.DeanJdbcUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ExcelImpExpServiceImpl implements DataFileImpExpService {
    private static final Logger logger = LoggerFactory.getLogger(ExcelImpExpServiceImpl.class);

    private static final Integer defaultFetchSize = 4000;
    // 导出限制记录数为50万
    private static final Integer EXP_LIMIT_COUNT = 500000;

    private Connection connection;

    private PreparedStatement ps;

    @Override
    public void imp(DeanImportInfo importInfo) {
        String filePath = importInfo.getFilePath();
        List<ReadSheet> sheets = EasyExcel.read(filePath).build().excelExecutor().sheetList();
        // 获取所有sheet
        ExcelImpListner deanEasyExcelListener = new ExcelImpListner(importInfo);
        try(FileInputStream fileInputStream = new FileInputStream(filePath);){
        // 导入所有的sheet
            for (ReadSheet sheet : sheets) {
                importInfo.setSheetName(sheet.getSheetName());
                EasyExcel.read(fileInputStream,deanEasyExcelListener).sheet(sheet.getSheetName()).headRowNumber(importInfo.getSkipHead()).doRead();
                logger.info("文件:{},sheetName:{}已处理完成,完成时间:{}",importInfo.getFilePath(),importInfo.getSheetName(), LocalDateTime.now());
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        logger.info("文件:{}已处理完成,完成时间:{}",importInfo.getFilePath(), LocalDateTime.now());
    }

    @Override
    public void exp(DeanExportInfo exportInfo) {
        String fileName = exportInfo.getFilePath();
        String sql = exportInfo.getQuerySql();
        String connName = exportInfo.getConnName();
        Integer fetchSize = exportInfo.getFetchSize()==null?defaultFetchSize:exportInfo.getFetchSize();
        String charSet = exportInfo.getCharSet();
        logger.debug("文件[{}]已开始导出,sql[{}],connName[{}],fetchSize[{}],开始时间[{}]",fileName,sql,connName,fetchSize, LocalDateTime.now());

        SysDbConnInfo connInfo = DeanToolDao.getInstance().queryConnInfo(connName);
        // 导出数据量查询 如果导出的数据量大于限制的数据量则不予导出
        Map<String,Object> countResult = DeanJdbcUtils.queryForMap(connInfo,"select count(1) LIMIT_COUNT from ("+sql+") tmp");
        Integer countLimt = Integer.valueOf(countResult.get("LIMIT_COUNT").toString());
        if(countLimt.compareTo(EXP_LIMIT_COUNT) == 1){
            logger.error("导出数据的记录数【{}】超过excel限制的记录数【{}】,放弃本批数据导出",countLimt,EXP_LIMIT_COUNT);
            throw DeanRuntimeException.throwException(996,"导出数据的记录数超过excel限制的记录数,放弃本批数据导出");
        }
        List<List<String>> datas = new ArrayList<List<String>>();
        List<String> header = new ArrayList<String>();
        ExcelWriter writer = EasyExcel.write(fileName).build();
        try(Connection connection = DeanJdbcUtils.getConnection(connInfo);) {
            WriteSheet sheet = new WriteSheet();
            sheet.setSheetNo(0);
            sheet.setSheetName("1");
            PreparedStatement ps = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
            ps.setFetchSize(fetchSize);
            ps.setFetchDirection(ResultSet.FETCH_REVERSE);
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData resMetaData = rs.getMetaData();
            // 表头处理
            int columnCount = resMetaData.getColumnCount();
            for(int i = 0;i < columnCount; i++){
                header.add(resMetaData.getColumnName(i+1));
            }
            datas.add(header);
            int counter = 1;
            // 处理数据
            while (rs.next()){
                List<String> data = new ArrayList<String>();
                for (int i = 0; i < columnCount; i++) {
                    data.add(rs.getString(i+1));
                }
                datas.add(data);
                counter++;
                if(counter%fetchSize == 0){
                    writer.write(datas,sheet);
                    logger.debug("共写入[{}]条数据,完成时间[{}]",counter, LocalDateTime.now());
                    datas.clear();
                }
            }
            if(counter%fetchSize > 0){
                writer.write(datas,sheet);
                logger.debug("共导出[{}]条数据,完成时间[{}]",counter, LocalDateTime.now());
            }
            writer.finish();
            logger.debug("文件[{}]已完成导出,完成时间[{}] bye bye ......",fileName, LocalDateTime.now());

        } catch (SQLException throwables) {
            logger.error("生成exce文件失败,失败原因:{}",throwables.getMessage(),throwables);
            throw DeanRuntimeException.throwException(996,throwables.getMessage());
        } catch (ClassNotFoundException e) {
            logger.error("生成excel文件失败,失败原因:{}",e.getMessage(),e);
            throw DeanRuntimeException.throwException(996,e.getMessage());
        }
    }


    /**
     * 保存结果到数据库
     * @param sql
     * @param datas
     * @param connectInfo
     */
    private  void saveDb(String sql, List<List<String>> datas, SysDbConnInfo connectInfo) {
        try{
            if (connection == null) {
                connection = DeanJdbcUtils.getConnection(connectInfo);
                connection.setAutoCommit(false);
                ps = DeanJdbcUtils.getPreparedStatement(connection, sql);
            }
            for (List<String> data : datas) {
                int size = data.size();
                for (int i = 0; i < size; i++) {
                    ps.setString(i + 1, data.get(i));
                }
                ps.addBatch();
            }
            ps.executeBatch();
            ps.clearBatch();
            connection.commit();
            // DeanJdbcUtils.batchInsert(connInfo,sql,data);
        } catch (ClassNotFoundException e) {
            logger.error(e.getMessage(),e);
            DeanRuntimeException.throwException(996,e.getMessage());
        } catch (SQLException throwables) {
            logger.error(throwables.getMessage(),throwables);
            try {
                connectInfo.setClose(true);
                connection.rollback();
            } catch (SQLException e) {
                logger.error(e.getMessage(),e);
            }
            DeanRuntimeException.throwException(996,throwables.getMessage());
        }finally {
            // 关闭连接
            if(connectInfo.getClose()){
                DeanJdbcUtils.close(ps,connection);
                connection = null;
            }
        }

    }
}
